package com.iweb.education.student.Dao;

import com.iweb.education.Utils.JdbcUtil;
import com.iweb.education.student.Model.Student;

import java.util.List;
import java.util.Map;

public class StudentDaoImpl implements StudentDao{
    @Override
    public Student selectBySidSpassword(Integer sid, String spassword) {
        String sql="select sid,sname,ssex,sphone,sage,sadress,spassword from student where sid=? and spassword=?";
        List<Map<String,Object>> list= JdbcUtil.queryBySql(sql,sid,spassword);
        if (list.size()>0){
            Map<String,Object> map=list.get(0);
            String sname=(String) map.get("sname");
            String ssex=(String)map.get("ssex");
            String sphone=(String) map.get("sphone");
            Integer sage=(Integer) map.get("sage");
            String sadress=(String) map.get("sadress");
            Student student=new Student(sid,sname,ssex,sphone,sage,sadress,spassword);
            return student;

        }
        return null;
    }
    @Override
    public Map<String, Object> showPersonalInfo(Integer sid) {
        String sql = "select sid,sname,ssex,sphone,sage,sadress from student where sid=? ";
        List<Map<String, Object>> list = JdbcUtil.queryBySql(sql, sid);
        return list.isEmpty() ? null : list.get(0);
    }
    @Override
    public List<Map<String, Object>> getGrades(Integer sid) {
        String sql = "SELECT c.cname, c.cval, s.cmark, s.tid " +
                "FROM score s " +
                "JOIN course c ON s.cid = c.cid " +
                "WHERE s.sid = ?";
        return JdbcUtil.queryBySql(sql, sid);
    }
    @Override
    public List<Map<String, Object>> getCourseList() {
        String sql = "SELECT c.cid, c.cname, c.ctime, t.teachername, " +
                "c.cval, c.cnumber " +
                "FROM course c " +
                "LEFT JOIN teacher t ON c.tid = t.teacherid " +
                "WHERE c.cstatus = 1";
        return JdbcUtil.queryBySql(sql);
    }

    @Override
    public boolean selectCourse(Integer sid, Integer cid) {
        String sql = "INSERT INTO score (sid, cid) VALUES (?, ?)";
        return JdbcUtil.updateBySql(sql, sid, cid) > 0;
    }

    @Override
    public boolean dropCourse(Integer sid, Integer cid) {
        String sql = "DELETE FROM score WHERE sid = ? AND cid = ?";
        return JdbcUtil.updateBySql(sql, sid, cid) > 0;
    }

    @Override
    public boolean isSelectedCourse(Integer sid, Integer cid) {
        String sql = "SELECT COUNT(*) as count FROM score WHERE sid = ? AND cid = ?";
        List<Map<String, Object>> list = JdbcUtil.queryBySql(sql, sid, cid);
        return list.get(0).get("count") != null && ((Long)list.get(0).get("count")) > 0;
    }

    @Override
    public boolean updateCourseNumber(Integer cid, boolean isIncrease) {
        String sql = isIncrease ?
                "UPDATE course SET cnumber = cnumber + 1 WHERE cid = ?" :
                "UPDATE course SET cnumber = cnumber - 1 WHERE cid = ?";
        return JdbcUtil.updateBySql(sql, cid) > 0;
    }
}
